/*DONE* Table [dbo].[<CompanyName>$KOT Header$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 14.1.2021 - JI ******/
/*Add prestaging tables for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT
		MAX([Company])
	FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT
		LSInsight$Companies.[CompanyPrefix]
	FROM dbo.LSInsight$Companies
	WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].[' + @Company + '$KOT Header$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC (@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].[' + @Company + '$KOT Header$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[KOT No_] [nvarchar](20) NOT NULL,
	[ID] [int] NOT NULL,
	[Hospitality Type ID] [int] NOT NULL,
	[Hospitality Type Text] [nvarchar](50) NOT NULL,
	[Ordering Time] [datetime] NOT NULL,
	[Payment Status] [int] NOT NULL,
	[Employee Name] [nvarchar](50) NOT NULL,
	[Transaction Number] [nvarchar](20) NOT NULL,
	[POS ID] [nvarchar](20) NOT NULL,
	[Menu Type Description] [nvarchar](100) NOT NULL,
	[Text2] [nvarchar](100) NOT NULL,
	[Text3] [nvarchar](100) NOT NULL,
	[Text4] [nvarchar](100) NOT NULL,
	[Text5] [nvarchar](100) NOT NULL,
	[Text6] [nvarchar](100) NOT NULL,
	[Voided] [tinyint] NOT NULL,
	[Override Style] [nvarchar](20) NOT NULL,
	[Dirty] [tinyint] NOT NULL,
	[Recalled] [tinyint] NOT NULL,
	[RushOrder] [tinyint] NOT NULL,
	[For Printer Station] [tinyint] NOT NULL,
	[Printer Station ID] [nvarchar](20) NOT NULL,
	[Covers] [int] NOT NULL,
	[Comment] [nvarchar](100) NOT NULL,
	[Date] [datetime] NOT NULL,
	[On Hold Status] [int] NOT NULL,
	[Delivery_Takeout Order] [tinyint] NOT NULL,
	[On Hold Offset (Min_)] [decimal](38, 20) NOT NULL,
	[Restaurant No_] [nvarchar](10) NOT NULL,
	[Training Mode] [tinyint] NOT NULL,
	[Pre-Order Print DateTime] [datetime] NOT NULL,
	[KOT Time in Minutes] [decimal](38, 20) NOT NULL,
	[Date Time Due] [datetime] NOT NULL,
	[Prod_ Start Date Time] [datetime] NOT NULL,
	[KOT Max_ Prod_ Time] [decimal](38, 20) NOT NULL,
	[KOT Sum of Prod_ Times] [decimal](38, 20) NOT NULL,
	[Initial Table Descr_] [nvarchar](30) NOT NULL,
	[#FONT_ID#] [nvarchar](20) NOT NULL,
	[Trans_ Comment] [nvarchar](100) NOT NULL,
	[Sales Type] [nvarchar](20) NOT NULL,
	[Modified (Internal Use Only)] [tinyint] NOT NULL,
	[Date KOT Created] [datetime] NOT NULL,
	[Time KOT Created] [datetime] NOT NULL,
	[Serve KOTs for KDS Printer] [tinyint] NOT NULL,
	[Menu Type Code] [nvarchar](1) NOT NULL,
	[Print to KDS] [tinyint] NOT NULL,
	[Reprinted Count] [int] NOT NULL,
	[Hosp_ Type Sequence] [int] NOT NULL,
	[Customer Order Queue No_] [nvarchar](20) NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Customer Name] [nvarchar](20) NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
 CONSTRAINT [' + @Company + '$KOT Header$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[KOT No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [' + @Company + '$KOT Header$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i + 1
END
